sub SELECT - Mailing list pgsql-sql

Hi,

I think I should be able to implement a subquerie or subSELECT to retrieve a set of data from my tables, but have not quite figured it out.  I would appreciate any ideas/suggestions.

Three TABLES involved: clone, clone_contig(relational) and contig.

Example:

clone.(project plate p_row p_column read ver) uniquely defines a particular clone.

A search using 4 of these 6 columns [clone.(project plate p_row p_column) = (894 001 A 01)] will return 2 results:

        894 001 A 01 x 1

        894 001 A 01 y 1

where 'x|y' = clone.read, and '1' = clone.ver.

For each of these two (894001A01x1, 894001A01y1), I want to find the corresponding contig.

So, a two part query:

        1st find ALL clones defined by clone.(project plate p_row p_column)

        2nd find ALL contigs related to each clone.

The problem I have had is that the first/internal query returns multiple values,

    $result = $conn->exec(

    "SELECT contig.assembly_date,contig.contig_no,contig.ver
     FROM clone JOIN clone_contig USING (clone_id)

     WHERE  clone.clone_id = (
                  SELECT clone.clone_id
                   FROM clone
                      WHERE clone.project = '1024' AND
                        clone.plate = '001' AND
                clone.p_row = 'A' AND
                   clone.p_column = '01'
                   )
");

CREATE TABLE "clone" (
   "clone_id" int4 DEFAULT nextval('"clone_clone_id_seq"'::text) NOT NULL,

   "project" int4 NOT NULL,
   "plate" int4 NOT NULL,
   "p_row" char(1) NOT NULL,
   "p_column" int4 NOT NULL,
   "read" char(1) NOT NULL,
   "ver" int4 NOT NULL,

   "seq" text NOT NULL,
   "qual" text NOT NULL,
   "length" int4 NOT NULL,
   "qual_length" int4 NOT NULL,
   "mean_qual_after_trim" int4 NOT NULL,
   "qual_start" int4 NOT NULL,
   "qual_end" int4 NOT NULL,
   "comment" text NOT NULL,
   "gb_accessions_id" int4,
   CONSTRAINT "clone_pkey" PRIMARY KEY ("clone_id")
);

CREATE  UNIQUE INDEX "clone_project_key" ON "clone" ("p_column", "p_row", "plate", "project", "read", "ver");

CREATE TABLE "clone_contig" (
   "clone_id" int4,
   "contig_id" int4
);
CREATE  UNIQUE INDEX "clone_contig_clone_id_key" ON "clone_contig" ("clone_id", "contig_id");

CREATE TABLE "contig" (
   "contig_id" int4 DEFAULT nextval('"contig_contig_id_seq"'::text) NOT NULL,
   "assembly_date" date NOT NULL,
   "contig_no" int4 NOT NULL,
   "ver" int4 NOT NULL,
   "length" int4 NOT NULL,
   "seq" text NOT NULL,
   CONSTRAINT "contig_pkey" PRIMARY KEY ("contig_id")
);
CREATE  UNIQUE INDEX "contig_assembly_date_key" ON "contig" ("assembly_date", "contig_no", "ver");

--
Regards,

     Chuck